/** 
 * LoginDAO.java
 * 
 * 1.0
 * 
 * 04/07/2014
 * 
 * Copyright (c) 2013 FPT Software.
 * FPT Building, Street No1, DaNang Industrial Zone, AnDon, 
 * AnHaiBac Ward, SonTra District, DaNang City.
 * All rights reserved.
 */

package com.jms.fu.model.dao;

import java.io.UnsupportedEncodingException;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jms.fu.common.Common;
import com.jms.fu.common.Constant;
import com.jms.fu.exception.DAOException;
import com.jms.fu.model.bean.AppDownloaded;
import com.jms.fu.model.bean.User;

/**
 * Class to implement Data Access Object of module Login
 * 
 * @author Design Framework Group
 */
public class UserDAO {

	// Init variable
	Connection conn = null;
	PreparedStatement pStm = null;
	Statement stm = null;
	ResultSet rs = null;

	/**
	 * Method to check Login
	 * 
	 * @param username
	 *            ,password
	 * @return an int indicates user role in system 0:admin, 1:dev, 2:member if
	 *         username and password is valid, return -1 when login failed
	 * @throws DAOException
	 */
	public int checkLogin(String username, String password) throws DAOException {

		try {
			conn = ConnectionFactory.getConnection();
			String sql = "select username, role from tb_User where username = ? and password = ?";
			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			pStm.setString(2, Common.encryptPassword(password));

			rs = pStm.executeQuery();

			if (rs.next()) {
				return rs.getInt("role"); // role 0:admin, 1:dev,
											// 2:member
			} else {
				return -1; // can not login
			}

		} catch (SQLException | NoSuchAlgorithmException
				| UnsupportedEncodingException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pStm != null)
					pStm.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}

		}
	}

	/**
	 * Method to check status of username
	 * 
	 * @param account
	 * 
	 * @return if status form table tb_User is 1 return 1 and return 0 when
	 *         status is 0
	 * 
	 * @throws DAOException
	 */
	public int checkStatus(String username) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT status FROM tb_User WHERE username =?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			rs = pStm.executeQuery();

			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return 0;
	}

	public boolean checkUsername(String username) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT username FROM tb_User WHERE username =?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			rs = pStm.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return false;
	}

	public void addNewUser(String username, String password, String name,
			String email) throws DAOException {
		try {
			String passwordEncrypted = Common.encryptPassword(password);
			conn = ConnectionFactory.getConnection();
			String sql = "INSERT INTO tb_User (username,password,role,status,email,name) VALUES (?,?,2,0,?,?)";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			pStm.setString(2, passwordEncrypted);
			pStm.setString(3, email);
			pStm.setString(4, name);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (NoSuchAlgorithmException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public void changePassword(String username, String password)
			throws DAOException {
		try {
			String passwordEncrypted = Common.encryptPassword(password);
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET password=? WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(2, username);
			pStm.setString(1, passwordEncrypted);

			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (NoSuchAlgorithmException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public boolean checkPassword(String username, String password)
			throws DAOException {
		try {
			String passwordEncrypted = Common.encryptPassword(password);
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT password FROM tb_User WHERE username =?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			rs = pStm.executeQuery();
			while (rs.next()) {
				if (passwordEncrypted.equals(rs.getString(1))) {
					return false;
				}
			}

		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (NoSuchAlgorithmException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return true;

	}

	public User getUserInfoByUsername(String username) throws DAOException {
		User user = new User();
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "SELECT * FROM tb_User WHERE username =?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			rs = pStm.executeQuery();
			if (rs.next()) {
				user.setUsername(rs.getString(1));
				user.setRole(rs.getInt(3));
				user.setEmail(rs.getString(5));
				user.setName(rs.getString(6));
				user.setBankAccountNo(rs.getString(7));
				user.setBankAccountName(rs.getString(8));
				user.setBankName(rs.getString(9));
				user.setBalance(rs.getFloat(10));
				user.setPaypal(rs.getString(11));
			}
			return user;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public void updateMemberInfo(String username, String name, String email)
			throws DAOException {
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET name=?,email=? WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, name);
			pStm.setString(2, email);
			pStm.setString(3, username);

			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public void updateDevInfo(String username, String name, String email,
			String bankAccountNo, String bankAccountName, String bankName,String paypal)
			throws DAOException {
		try {

			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET name=?,email=?,bankAccountNo=?,bankAccountName=?,bankName=?,paypal=? WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, name);
			pStm.setString(2, email);
			pStm.setString(3, bankAccountNo);
			pStm.setString(4, bankAccountName);
			pStm.setString(5, bankName);
			pStm.setString(6, paypal);
			pStm.setString(7, username);

			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}

	}

	public void goDev(String username, String bankAccountNo,
			String bankAccountName, String bankName,float balance,String paypal) throws DAOException {
		try {
			float newBalance = balance - Constant.GO_DEV_PRICE;
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET bankAccountNo=?,bankAccountName=?,bankName=?,balance=?,role=1,paypal=? WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, bankAccountNo);
			pStm.setString(2, bankAccountName);
			pStm.setString(3, bankName);
			pStm.setString(6, username);
			pStm.setFloat(4, newBalance);
			pStm.setString(5, paypal);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		
	}

	public ArrayList<User> getAllUser() throws DAOException {
		User temp;
		ArrayList<User> list = new ArrayList<>();
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "SELECT username,role,status,name,email,bankAccountNo,bankAccountName,bankName,balance FROM tb_User WHERE username!='admin'";
			pStm = conn.prepareStatement(sql);
			rs=pStm.executeQuery();
			while(rs.next()){
				temp= new User();
				temp.setUsername(rs.getString(1));
				temp.setRole(rs.getInt(2));
				temp.setStt(rs.getInt(3));
				temp.setName(rs.getString(4));
				temp.setEmail(rs.getString(5));
				temp.setBankAccountNo(rs.getString(6));
				temp.setBankAccountName(rs.getString(7));
				temp.setBankName(rs.getString(8));
				temp.setBalance(rs.getFloat(9));
				list.add(temp);
			}
			return list;
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
	}

	public void blockUser(String username) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET status='1' WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		
	}

	public void unblockUser(String username) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET status='0' WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		
	}

	public void deleteUser(String username) throws DAOException {
		try {
			conn = ConnectionFactory.getConnection();
			String sql = "DELETE FROM tb_User WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setString(1, username);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		
	}

	public void deposit(String username, float newBalance) throws DAOException {
		try {
			
			conn = ConnectionFactory.getConnection();
			String sql = "UPDATE tb_User SET balance=? WHERE username=?";

			pStm = conn.prepareStatement(sql);
			pStm.setFloat(1, newBalance);
			pStm.setString(2, username);
			pStm.executeUpdate();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());

		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pStm != null) {
					pStm.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		
	}

//	public List<AppDownloaded> getListAppDown(String username) throws DAOException {
//		AppDownloaded temp;
//		ArrayList<AppDownloaded> list = new ArrayList<>();
//		try {
//			conn = ConnectionFactory.getConnection();
//			String sql = "SELECT appDownId,appName,datedownload,downVersion FROM tb_AppDown INNER JOIN tb_App ON tb_App.appId = tb_AppDown.appID"
//+"WHERE userID =?";
//			pStm = conn.prepareStatement(sql);
//			rs=pStm.executeQuery();
//			while(rs.next()){
//				
//			}
//			return list;
//		} catch (SQLException e) {
//			throw new DAOException(e.getMessage());
//		} finally {
//			try {
//				if (rs != null) {
//					rs.close();
//				}
//				if (pStm != null) {
//					pStm.close();
//				}
//				if (conn != null) {
//					conn.close();
//				}
//			} catch (SQLException e) {
//				throw new DAOException(e.getMessage());
//			}
//		}
//	}

}
